package persistencia;

import java.util.Vector;
import negocio.Cliente;
import negocio.ImobiliariaException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

	public class ListaClienteDAO extends BaseDAO {

        private PreparedStatement stIncluir;
        private PreparedStatement stConsultaNome;
        private PreparedStatement stConsultaDataNasc;
        private PreparedStatement stConsultaCpf;
        private PreparedStatement stListaNome;
        private PreparedStatement stAlterar;

        public ListaClienteDAO() throws ImobiliariaException {
            super();
            Connection conect = getConexao();
            try {
                stIncluir = conect.prepareStatement("INSERT INTO Cliente (nome, datanasc, endereco, cpfcliente, telefone, rg, email, estadocivil, renda) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ? )");
                stConsultaNome = conect.prepareStatement("SELECT * FROM cliente WHERE nome LIKE %?%");
                stConsultaDataNasc = conect.prepareStatement("SELECT * FROM cliente WHERE datanasc = ?");
                stConsultaCpf = conect.prepareStatement("SELECT * FROM cliente WHERE cpfcliente = ?");
                stListaNome = conect.prepareStatement("SELECT * FROM produtos ORDER BY nome");
                stAlterar = conect.prepareStatement("UPDATE cliente SET nome=?, datanasc=?, endereco=?, cpfcliente=?, telefone=?, rg=?, email=?, estadocivil=?,renda=? WHERE cpfcliente = ?");
            } catch(SQLException e) {
                throw new ImobiliariaException("Nao foi possivel criar as consultas dos clientes: " + e.getMessage());
            }
        }

	public void incluir(Cliente c) throws ImobiliariaException {
		try {
             stIncluir.setString(1, c.getNome());
             stIncluir.setString(2, c.getDataNasc());
             stIncluir.setString(3, c.getEndereco());
             stIncluir.setString(4, c.getCpf());
             stIncluir.setString(5, c.getTelefone());
             stIncluir.setString(6, c.getRg());
             stIncluir.setString(7, c.getEmail());
             stIncluir.setString(8, c.getEstadoCivil());
             stIncluir.setDouble(9, c.getRenda());
             stIncluir.executeUpdate();
               } catch(SQLException e) {
                   throw new ImobiliariaException("Erro ao incluir novo cliente: " + e.getMessage());
               }
               
              System.out.println(c);
	} 

	public Cliente[] consultaPorNome(String nome) throws ImobiliariaException {
				
			Vector v = new Vector();
		try {
             stConsultaNome.setString(1, nome);
             ResultSet rs = stConsultaNome.executeQuery();
             	while(rs.next()) {
                      Cliente c = new Cliente();
                      c.setNome(rs.getString("nome"));
                      c.setDataNasc(rs.getString("datanasc"));
                      c.setEndereco(rs.getString("endereco"));
                      c.setCpf(rs.getString("cpfcliente"));
                      c.setTelefone(rs.getString("telefone"));
                      c.setRg(rs.getString("rg"));
                      c.setEmail(rs.getString("email"));
                      c.setEstadoCivil(rs.getString("estadocivil"));
                      c.setRenda(rs.getFloat("renda"));
                      v.add(c);
                     }
		} catch(SQLException e) {
			throw new ImobiliariaException("Erro ao executar consulta de cliente por nome: " + e.getMessage());
		}
	
		return (Cliente[]) v.toArray(new Cliente[0]);
	}
	
	
	public Cliente consultaDataNasc(String dataNasc) throws ImobiliariaException {
		try {
             stConsultaDataNasc.setString(1, dataNasc);
             ResultSet rs = stConsultaDataNasc.executeQuery();
             	while(rs.next()) {
                      Cliente c = new Cliente();
                      c.setNome(rs.getString("nome"));
                      c.setDataNasc(rs.getString("datanasc"));
                      c.setEndereco(rs.getString("endereco"));
                      c.setCpf(rs.getString("cpfcliente"));
                      c.setTelefone(rs.getString("telefone"));
                      c.setRg(rs.getString("rg"));
                      c.setEmail(rs.getString("email"));
                      c.setEstadoCivil(rs.getString("estadocivil"));
                      c.setRenda(rs.getFloat("renda"));
                      return c;
                        }
		} catch(SQLException e) {
			throw new ImobiliariaException("Erro ao executar consulta de cliente por data de nascimento: " + e.getMessage());
		}
	
		return null;
	} 
	
	
	public Cliente consultaPorCpf(String nome) throws ImobiliariaException {
		try {
             stConsultaCpf.setString(1, nome);
             ResultSet rs = stConsultaCpf.executeQuery();
             	while(rs.next()) {
                      Cliente c = new Cliente();
                      c.setNome(rs.getString("nome"));
                      c.setDataNasc(rs.getString("datanasc"));
                      c.setEndereco(rs.getString("endereco"));
                      c.setCpf(rs.getString("cpfcliente"));
                      c.setTelefone(rs.getString("telefone"));
                      c.setRg(rs.getString("rg"));
                      c.setEmail(rs.getString("email"));
                      c.setEstadoCivil(rs.getString("estadocivil"));
                      c.setRenda(rs.getFloat("renda"));
                      return c;
                    }
		} catch(SQLException e) {
			throw new ImobiliariaException("Erro ao executar consulta de cliente por CPF: " + e.getMessage());
		}
	
		return null;
	}
	

	public Cliente[] listaNome() throws ImobiliariaException {
		Vector<Cliente> result = new Vector<Cliente>();
		try {
             ResultSet rs = stListaNome.executeQuery();
             	while(rs.next()) {
                      Cliente c = new Cliente();
                      c.setNome(rs.getString("nome"));
                      c.setDataNasc(rs.getString("datanasc"));
                      c.setEndereco(rs.getString("endereco"));
                      c.setCpf(rs.getString("cpfcliente"));
                      c.setTelefone(rs.getString("telefone"));
                      c.setRg(rs.getString("rg"));
                      c.setEmail(rs.getString("email"));
                      c.setEstadoCivil(rs.getString("estadocivil"));
                      c.setRenda(rs.getFloat("renda"));
                      result.add(c);
                    }
		} catch(SQLException e) {
			throw new ImobiliariaException("Erro ao consultar clientes cadastrados: " + e.getMessage());
		}
		
		return result.toArray(new Cliente[0]);
	}
	
	public void alterar(Cliente c) throws ImobiliariaException {
		try {
			stAlterar.setString(1, c.getNome());
			stAlterar.setString(2, c.getDataNasc());
			stAlterar.setString(3, c.getEndereco());
			stAlterar.setString(4, c.getCpf());
			stAlterar.setString(5, c.getTelefone());
			stAlterar.setString(6, c.getRg());
			stAlterar.setString(7, c.getEmail());
			stAlterar.setString(8, c.getEstadoCivil());
			stAlterar.setFloat(9, c.getRenda());
            stAlterar.executeUpdate();
            
		} catch(SQLException e) {
			throw new ImobiliariaException("Erro ao alterar dados do cliente: " + e.getMessage());
		} 		
	}



	}